<?php

require_once ('Site.class.php');
require_once ('Article.class.php');
require_once ('ArticleManagerException.class.php');
require_once ('Category.class.php');

class ArticleManager {

    private $connection;

    const NUM_PER_PAGE = 10;
    const QUERY_SAVE_ARTICLE = "INSERT INTO articles (title, abstract, content, image, author, date, time, creator, links) VALUES (:title, :abstract, :content, :image, :author, :date, :time, :creator, :links);";
    const QUERY_CHECK_TAG_EXISTANCE = "SELECT * FROM tags WHERE tag = :tag;";
    const QUERY_GET_ARTICLE_BY_ID = "SELECT * FROM articles WHERE id = :id;";
    const QUERY_GET_TAGS_BY_ARTICLE_ID = "SELECT tag FROM articles_tags WHERE article_id = :id;";
    const QUERY_GET_STUFFS_BY_ARTICLE_ID = "SELECT stuff_id FROM articles_stuffs WHERE article_id = :id;";
    const QUERY_GET_ARTICLES_BY_CATEGORY_ID = "SELECT article_id FROM articles_categories INNER JOIN articles ON articles.id = articles_categories.article_id WHERE category_id = :category_id ORDER BY date DESC, time DESC LIMIT :start, 10;";
    const QUERY_GET_ARTICLES_BY_TAG = "SELECT article_id FROM articles_tags INNER JOIN articles ON articles.id = articles_tags.article_id WHERE tag = :tag ORDER BY date DESC, time DESC LIMIT :start, 10;";
    const QUERY_GET_CATEGORIES_BY_ARTICLE_ID = "SELECT category_id FROM articles_categories WHERE article_id = :id;";
    const QUERY_GET_ALL_ARTICLES_PAGINATION = "SELECT * FROM articles ORDER BY date DESC, time DESC LIMIT :start, 10;";
    const QUERY_GET_NUM_PAGES_CATEGORY = "SELECT COUNT(*) AS num_articles FROM articles_categories WHERE category_id = :id;";
    const QUERY_GET_NUM_PAGES_TAG = "SELECT COUNT(*) AS num_articles FROM articles_tags WHERE tag = :id;";
    const QUERY_GET_NUM_PAGES_ARCHIVE = "SELECT COUNT(*) AS num_articles FROM articles;";
    const QUERY_GET_CATEGORIES = "SELECT * FROM categories;";
    const QUERY_GET_LAST_N_ARTICLES = "SELECT id FROM articles ORDER BY date DESC, time DESC LIMIT 5;";
    const QUERY_GET_TAGS = "SELECT * FROM tags ORDER BY tag ASC;";
    public function __construct() {
        $this->connection = new PDO(
                        "mysql:host=" . Site::DB_HOST . ";dbname=" . Site::DB_NAME,
                        Site::DB_USERNAME,
                        Site::DB_PASSWORD,
                        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
        );
    }

    public function save_article(&$article) {
        try {
            $this->connection->beginTransaction();
            $statement = $this->connection->prepare(self::QUERY_SAVE_ARTICLE);
            $statement->execute(array('title' => $article->title,
                'abstract' => $article->abstract,
                'content' => $article->content,
                'image' => $article->image,
                'author' => $article->author,
                'date' => $article->date->format('Y-m-d'),
                'time' => $article->time->format('H:i:s'),
                'creator' => $article->creator,
                'links' => $article->links));

            $article_id = $this->connection->lastInsertId();

            $this->save_tags($article_id, $article->tags);
            $this->save_categories($article_id, $article->categories);
            $this->save_stuffs($article_id, $article->stuffs);

            $article->id = $article_id;
            $this->connection->commit();
        } catch (Exception $e) {
            $this->connection->rollBack();
        }
    }

    public function update_article($old_article, $mod_article) {
        try {
            $this->connection->beginTransaction();
            if (get_class($old_article) != 'Article' || get_class($mod_article) != 'Article')
                throw new Exception();
            if ($old_article->id != $mod_article->id)
                throw new ArticleManagerException("The articles' ids don't match! I'cannot modify different articles!", ArticleManagerException::ARTICLES_ID_NOT_MATCH);

            $changes = array('title' => FALSE,
                'abstract' => FALSE,
                'content' => FALSE,
                'image' => FALSE,
                'author' => FALSE,
                'links' => FALSE,
            );
            if ($old_article->title != $mod_article->title)
                $changes['title'] = TRUE;
            if ($old_article->abstract != $mod_article->abstract)
                $changes['abstract'] = TRUE;
            if ($old_article->content != $mod_article->content)
                $changes['content'] = TRUE;
            if ($old_article->image != $mod_article->image)
                $changes['image'] = TRUE;
            if ($old_article->author != $mod_article->author)
                $changes['author'] = TRUE;
            if ($old_article->links != $mod_article->links)
                $changes['links'] = TRUE;

            $article_statement = $this->connection->prepare($this->get_article_update_query($changes));
            $new_values = array('id' => $old_article->id);
            foreach ($changes as $key => $changed) {
                if ($changed)
                    $new_values[$key] = $mod_article->$key;
            }
            $article_statement->execute($new_values);

            $tags_to_delete = array_diff($old_article->tags, $mod_article->tags);
            if (count($tags_to_delete) > 0)
                $this->delete_tags($old_article->id, $tags_to_delete);

            $tags_to_add = array_diff($mod_article->tags, $old_article->tags);
            if (count($tags_to_add) > 0)
                $this->save_tags($old_article->id, $tags_to_add);

            $categories_to_delete = array_diff($old_article->categories, $mod_article->categories);
            if (count($categories_to_delete) > 0)
                $this->delete_categories($old_article->id, $categories_to_delete);

            $categories_to_add = array_diff($mod_article->categories, $old_article->categories);
            if (count($categories_to_add) > 0)
                $this->save_categories($old_article->id, $categories_to_add);

            $stuffs_to_delete = array_diff($old_article->stuffs, $mod_article->stuffs);
            if (count($stuffs_to_delete) > 0)
                $this->delete_stuffs($old_article->id, $stuffs_to_delete);

            $stuffs_to_add = array_diff($mod_article->stuffs, $old_article->stuffs);
            if (count($stuffs_to_add) > 0)
                $this->save_stuffs($old_article->id, $stuffs_to_add);

            $this->connection->commit();
        } catch (Exception $e) {
            $this->connection->rollBack();
        }
    }

    public function get_all_articles($page) {
        $statement = $this->connection->prepare(self::QUERY_GET_ALL_ARTICLES_PAGINATION);
        $statement->bindValue(':start', (int) ($page - 1) * self::NUM_PER_PAGE, PDO::PARAM_INT);
        $statement->execute();
        $result = array();
        $article = null;

        while ($a = $statement->fetch(PDO::FETCH_ASSOC)) {
            $article = new Article();
            $article->id = $a['id'];
            $article->title = $a['title'];
            $article->abstract = $a['abstract'];
            $article->content = $a['content'];
            $article->image = $a['image'];
            $article->author = $a['author'];
            $article->links = self::get_links_array($a['links']);
            $article->date = new DateTime($a['date']);
            $article->time = new DateTime($a['time']);
            $article->creator = $a['creator'];

            $article->tags = $this->get_tags_array_by_article_id($a['id']);
            $article->stuffs = self::get_stuffs_array_by_article_id($a['id']);
            $article->categories = self::get_categories_array_by_article_id($a['id']);

            array_push($result, $article);
        }

        return $result;
    }

    public function get_last_n_articles() {
        $statement = $this->connection->prepare(self::QUERY_GET_LAST_N_ARTICLES);
        $statement->execute();
        $result = array();
        while ($a = $statement->fetch(PDO::FETCH_ASSOC)) {
            array_push($result, $this->get_article_by_id($a['id']));
        }

        return $result;
    }

    public function get_article_by_id($id) {
        $statement = $this->connection->prepare(self::QUERY_GET_ARTICLE_BY_ID);
        $statement->execute(array('id' => $id));

        $article = null;

        if ($statement->rowCount() > 0) {
            $article_array = $statement->fetch(PDO::FETCH_ASSOC);

            $article = new Article();
            $article->id = $article_array['id'];
            $article->title = $article_array['title'];
            $article->abstract = $article_array['abstract'];
            $article->content = $article_array['content'];
            $article->image = $article_array['image'];
            $article->author = $article_array['author'];
            $article->links = self::get_links_array($article_array['links']);
            $article->date = new DateTime($article_array['date']);
            $article->time = new DateTime($article_array['time']);
            $article->creator = $article_array['creator'];

            $article->tags = $this->get_tags_array_by_article_id($id);
            $article->stuffs = self::get_stuffs_array_by_article_id($id);
            $article->categories = self::get_categories_array_by_article_id($id);
        }

        return $article;
    }

    public function get_articles_by_category($category, $page) {
        $statement = $this->connection->prepare(self::QUERY_GET_ARTICLES_BY_CATEGORY_ID);
        $statement->bindValue(':start', (int) ($page - 1) * self::NUM_PER_PAGE, PDO::PARAM_INT);
        $statement->bindValue(':category_id', $category, PDO::PARAM_INT);
        $statement->execute();
        $result = array();
        for ($i = 0; $article_id = $statement->fetch(PDO::FETCH_ASSOC); $i++) {
            $result[$i] = $this->get_article_by_id($article_id['article_id']);
        }

        return $result;
    }

    public function get_articles_by_tag($tag, $page) {
        $statement = $this->connection->prepare(self::QUERY_GET_ARTICLES_BY_TAG);
        $statement->bindValue(':start', (int) ($page - 1) * self::NUM_PER_PAGE, PDO::PARAM_INT);
        $statement->bindValue(':tag', $tag, PDO::PARAM_STR);
        $statement->execute();
        $result = array();
        for ($i = 0; $article_id = $statement->fetch(PDO::FETCH_ASSOC); $i++) {
            $result[$i] = $this->get_article_by_id($article_id['article_id']);
        }

        return $result;
    }

    public function get_num_pages($type, $type_id) {
        $statement = null;
        switch ($type) {
            case 'category':
                $statement = $this->connection->prepare(self::QUERY_GET_NUM_PAGES_CATEGORY);
                break;
            case 'tag':
                $statement = $this->connection->prepare(self::QUERY_GET_NUM_PAGES_TAG);
                break;
        }

        $statement->execute(array('id' => $type_id));
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        $num_articles = $result['num_articles'];
        return ceil($num_articles / self::NUM_PER_PAGE);
    }

    public function get_archive_num_pages() {
        $statement = $this->connection->prepare(self::QUERY_GET_NUM_PAGES_ARCHIVE);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        $num_articles = $result['num_articles'];
        return ceil($num_articles / self::NUM_PER_PAGE);
    }

    public function get_categories() {
        $statement = $this->connection->prepare(self::QUERY_GET_CATEGORIES);
        $statement->execute();
        $category = null;
        $result = array();
        while ($c = $statement->fetch(PDO::FETCH_ASSOC)) {
            $category = new Category();
            foreach ($c as $key => $value)
                $category->{$key} = $value;
            array_push($result, $category);
        }
        return $result;
    }
    
    public function get_tags() {
        $stmt = $this->connection->prepare(self::QUERY_GET_TAGS);
        $stmt->execute();
        
        $result = array();
        
        while ($t = $stmt->fetch(PDO::FETCH_ASSOC)) {
            array_push($result, $t['tag']);
        }
        
        return $result;
    }

    private function get_tags_array_by_article_id($id) {

        $statement = $this->connection->prepare(self::QUERY_GET_TAGS_BY_ARTICLE_ID);
        $statement->execute(array('id' => $id));

        $tags = array();
        for ($i = 0; $tag = $statement->fetch(PDO::FETCH_ASSOC); $i++)
            $tags[$i] = $tag['tag'];
        return $tags;
    }

    private function get_stuffs_array_by_article_id($id) {
        $statement = $this->connection->prepare(self::QUERY_GET_STUFFS_BY_ARTICLE_ID);
        $statement->execute(array('id' => $id));

        $stuffs = array();
        for ($i = 0; $stuff = $statement->fetch(PDO::FETCH_ASSOC); $i++)
            $stuffs[$i] = $stuff['stuff_id'];

        return $stuffs;
    }

    private function get_categories_array_by_article_id($id) {
        $statement = $this->connection->prepare(self::QUERY_GET_CATEGORIES_BY_ARTICLE_ID);
        $statement->execute(array('id' => $id));

        $categories = array();
        for ($i = 0; $category = $statement->fetch(PDO::FETCH_ASSOC); $i++)
            $categories[$i] = $category['category_id'];

        return $categories;
    }

    private function tag_exists($tag) {
        $tag_existance_statement = $this->connection->prepare(self::QUERY_CHECK_TAG_EXISTANCE);
        $tag_existance_statement->execute(array('tag' => $tag));
        return ($tag_existance_statement->rowCount() > 0);
    }

    private function get_query_values_array($array, $prefix) {
        $a = array();
        foreach ($array as $key => $value) {
            $a["$prefix$key"] = $value;
        }
        return $a;
    }

    private function get_article_update_query($changes) {
        $query = "UPDATE articles SET ";
        $i = 0;
        foreach ($changes as $key => $changed) {
            $i++;
            if ($changed) {
                $query.= "$key = :$key";
                if ($i < count($changes))
                    $query .= ", ";
            }
        }

        return $query.= " WHERE id = :id;";
    }

    private function get_new_tags_insertion_query($keys) {
        $query = "INSERT INTO tags (tag) VALUES ";
        $i = 0;
        foreach ($keys as $key) {
            $query .= "(:tag$key)";
            if (++$i < count($keys))
                $query .= ", ";
        }

        return $query .= ";";
    }

    private function get_new_stuffs_insertion_query($keys) {
        $query = "INSERT INTO stuffs (file_name) VALUES ";
        $i = 0;
        foreach ($keys as $key) {
            $query .= "(:stuff$key)";
            if (++$i < count($keys))
                $query .= ", ";
        }

        return $query .= ";";
    }

    private function get_articles_tags_insertion_query($keys) {
        $query = "INSERT INTO articles_tags (article_id, tag) VALUES ";
        $i = 0;
        foreach ($keys as $key) {
            $query .= "(:article_id, :tag$key)";
            if (++$i < count($keys))
                $query .= ", ";
        }

        return $query .= ";";
    }

    private function get_articles_tags_deletion_query($keys) {
        $query = "DELETE FROM articles_tags WHERE (article_id = :article_id) AND ";
        $i = 0;
        $query .= "(";
        foreach ($keys as $key) {
            $query .= "( tag = :tag$key )";
            if (++$i < count($keys))
                $query .= " OR ";
        }

        return $query .= ");";
    }

    private function get_articles_categories_insertion_query($keys) {
        $query = "INSERT INTO articles_categories (article_id, category_id) VALUES ";
        $i = 0;
        foreach ($keys as $key) {
            $query .= "(:article_id, :category$key)";
            if (++$i < count($keys))
                $query .= ", ";
        }

        return $query .= ";";
    }

    private function get_articles_categories_deletion_query($keys) {
        $query = "DELETE FROM articles_categories WHERE (article_id = :article_id) AND ";
        $i = 0;
        $query .= "(";
        foreach ($keys as $key) {
            $query .= "( category_id = :category$key )";
            if (++$i < count($keys))
                $query .= " OR ";
        }

        return $query .= ");";
    }

    private function get_articles_stuffs_insertion_query($keys) {
        $query = "INSERT INTO articles_stuffs (article_id, stuff_id) VALUES ";
        $i = 0;
        foreach ($keys as $key) {
            $query .= "(:article_id, :stuff$key)";
            if (++$i < count($keys))
                $query .= ", ";
        }

        return $query .= ";";
    }

    public function get_articles_stuffs_deletion_query($keys) {
        $query = "DELETE FROM articles_stuffs WHERE (article_id = :article_id) AND ";
        $i = 0;
        $query .= "(";
        foreach ($keys as $key) {
            $query .= "( stuff_id = :stuff$key )";
            if (++$i < count($keys))
                $query .= " OR ";
        }

        return $query .= ");";
    }

    private function save_tags($article_id, $tags) {
        $i = 0;
        $new_tags = array();
        foreach ($tags as $tag) {
            if (!$this->tag_exists($tag)) {
                $new_tags[$i++] = $tag;
            }
        }

        if (count($new_tags) > 0) {
            $new_tag_statement = $this->connection->prepare($this->get_new_tags_insertion_query(array_keys($new_tags)));
            $new_tag_statement->execute($this->get_query_values_array($new_tags, 'tag'));
            if ($new_tag_statement->rowCount() <= 0)
                throw new ArticleManagerException("Error occured while adding a new tag!", ArticleManagerException::GENERIC_TAG_ERROR);
        }

        $relation_tag_article_statement = $this->connection->prepare($this->get_articles_tags_insertion_query(array_keys($tags)));
        $rel_a_t = $this->get_query_values_array($tags, 'tag');
        $rel_a_t['article_id'] = $article_id;
        $relation_tag_article_statement->execute($rel_a_t);

        if ($relation_tag_article_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while adding a new relation between article and tag!", ArticleManagerException::GENERIC_TAG_ERROR);
    }

    private function delete_tags($article_id, $tags) {
        $deletion_a_t_statement = $this->connection->prepare($this->get_articles_tags_deletion_query(array_keys($tags)));
        $par = $this->get_query_values_array($tags, 'tag');
        $par['article_id'] = $article_id;
        $deletion_a_t_statement->execute($par);

        if ($deletion_a_t_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while deleting a tag from an article!", ArticleManagerException::GENERIC_TAG_ERROR);
    }

    private function save_categories($article_id, $categories) {
        $relation_category_article_statement = $this->connection->prepare($this->get_articles_categories_insertion_query(array_keys($categories)));
        $rel_a_c = $this->get_query_values_array($categories, 'category');
        $rel_a_c['article_id'] = $article_id;
        $relation_category_article_statement->execute($rel_a_c);

        if ($relation_category_article_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while adding a new relation between article and category!", ArticleManagerException::GENERIC_CATEGORY_ERROR);
    }

    private function delete_categories($article_id, $categories) {
        $relation_category_article_statement = $this->connection->prepare($this->get_articles_categories_deletion_query(array_keys($categories)));
        $rel_a_c = $this->get_query_values_array($categories, 'category');
        $rel_a_c['article_id'] = $article_id;
        $relation_category_article_statement->execute($rel_a_c);

        if ($relation_category_article_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while deleting a category from an article!", ArticleManagerException::GENERIC_CATEGORY_ERROR);
    }

    private function save_stuffs($article_id, $stuffs) {

        $relation_stuff_article_statement = $this->connection->prepare($this->get_articles_stuffs_insertion_query(array_keys($stuffs)));
        $rel_a_s = $this->get_query_values_array($stuffs, 'stuff');
        $rel_a_s['article_id'] = $article_id;
        $relation_stuff_article_statement->execute($rel_a_s);

        if ($relation_stuff_article_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while adding a new relation between article and stuff!", ArticleManagerException::GENERIC_STUFF_ERROR);
    }

    private function delete_stuffs($article_id, $stuffs) {
        $relation_stuff_article_statement = $this->connection->prepare($this->get_articles_stuffs_deletion_query(array_keys($stuffs)));
        $rel_a_s = $this->get_query_values_array($stuffs, 'stuff');
        $rel_a_s['article_id'] = $article_id;
        $relation_stuff_article_statement->execute($rel_a_s);

        if ($relation_stuff_article_statement->rowCount() <= 0)
            throw new ArticleManagerException("Error occured while deleting a stuff from an article!", ArticleManagerException::GENERIC_STUFF_ERROR);

        //Here it is going to be a function call to StuffManager to delete these stuffs from filesystem!
    }

    private static function get_links_array($string) {
        return ($string != "") ? explode(';', $string) : array();
    }

}

?>
